package nus.edu.sg.servlet;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.security.SecureRandom;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.SortedSet;
import java.util.TreeSet;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import nus.edu.sg.main.DBDashboardImpl;
import nus.edu.sg.main.IDBDashboard;
import nus.edu.sg.object.ParameterConstant;
import nus.edu.sg.object.SampleTimeConfiguration;

import org.apache.log4j.Logger;
import org.json.JSONArray;
import org.json.JSONObject;

/**
 * Servlet implementation class DBTuneServlet
 */
@WebServlet("/db_tune")
public class DBTuneServlet extends HttpServlet {
	private static DateFormat df = new SimpleDateFormat("MMMM-dd HH:mm");
	private static Logger _log = Logger.getLogger(DBTuneServlet.class);
	public static final String THRESHOLD_FILE = "WEB-INF/threshold.properties";
	private static final long serialVersionUID = 1L;

	private static final boolean DEBUG = false;
	public static final String[] PARAM_IDS = { "SHARED_POOL_SIZE",
			"BUFFER_CACHE", "REDO_LOG", "MEM_FOR_SORT" };
	public static Map<String, String> paramMap = new HashMap<String, String>();
	private SecureRandom random = new SecureRandom();
	private IDBDashboard dbDashboard;

	/**
	 * @see HttpServlet#HttpServlet()
	 */
	public DBTuneServlet() {
		super();
		paramMap.put(PARAM_IDS[0], "Share Pool Size");
		paramMap.put(PARAM_IDS[1], "Buffered Cache");
		paramMap.put(PARAM_IDS[2], "Redo Log Buffer/Files");
		paramMap.put(PARAM_IDS[3], "Memory Area Used for Sorting");
		dbDashboard = new DBDashboardImpl();
	}

	private JSONObject getSQLResult(String sqlCommand) {
		JSONObject jsonResult = new JSONObject();
		ArrayList<String[]> result;
		try {
			_log.debug("Executing sql command \"" + sqlCommand + "\"");
			if (DEBUG) {
				result = new ArrayList<String[]>();
				for (int i = 0; i < 10; i++) {
					String[] r = new String[10];
					for (int j = 0; j < 10; j++) {
						r[j] = String.valueOf(random.nextInt(100));
					}
					result.add(r);
				}
			} else {
				result = dbDashboard.getSqlResult(sqlCommand);
			}
			JSONArray jsonArray = new JSONArray();
			for (int i = 0; i < result.size(); i++) {
				JSONArray row = new JSONArray();
				for (int j = 0; j < result.get(i).length; j++) {
					row.put(result.get(i)[j]);
				}
				jsonArray.put(row);
			}
			jsonResult.put("rows", jsonArray);
		} catch (Exception e) {
			e.printStackTrace();
			jsonResult.put("error", e.getMessage());
		}
		return jsonResult;
	}

	public JSONObject setThreshold(int sharePoolMin, int sharePoolMax,
			int buffedCacheMin, int bufferedCacheMax, int redoLogMin,
			int redoLogMax, int memorySortMin, int memorySortMax) {
		_log.debug("Setting threshold: "
				+ "\n\twarning threshold for share pool: " + sharePoolMin
				+ "\n\talert threshold fro share pool: " + sharePoolMax
				+ "\n\twarning threshold fro buffer cache: " + buffedCacheMin
				+ "\n\talert threshold fro buffer cache: " + bufferedCacheMax
				+ "\n\twarning threshold fro redo log: " + redoLogMin
				+ "\n\talert threshold fro redo log: " + redoLogMax
				+ "\n\twarning threshold fro memory sort: " + memorySortMin
				+ "\n\talert threshold fro memory sort: " + memorySortMax);
		JSONObject jsonResult = new JSONObject();
		Properties thresholdProps = new Properties();
		thresholdProps.put(RequestConstants.SHARE_POOL_MIN,
				String.valueOf(sharePoolMin));
		thresholdProps.put(RequestConstants.SHARE_POOL_MAX,
				String.valueOf(sharePoolMax));
		thresholdProps.put(RequestConstants.BUFFERED_CACHE_MIN,
				String.valueOf(buffedCacheMin));
		thresholdProps.put(RequestConstants.BUFFERED_CACHE_MAX,
				String.valueOf(bufferedCacheMax));
		thresholdProps.put(RequestConstants.REDO_LOG_BUFFER_MIN,
				String.valueOf(redoLogMin));
		thresholdProps.put(RequestConstants.REDO_LOG_BUFFER_MAX,
				String.valueOf(redoLogMax));
		thresholdProps.put(RequestConstants.MEMORY_SORTING_MIN,
				String.valueOf(memorySortMin));
		thresholdProps.put(RequestConstants.MEMORY_SORTING_MAX,
				String.valueOf(memorySortMax));
		try {
			String filePath = getServletContext().getRealPath(THRESHOLD_FILE);
			FileOutputStream out = new FileOutputStream(filePath);
			thresholdProps.store(out,
					"---Threshold properties for dbtune parameters---");
			out.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			jsonResult.put("error", e.getMessage());
		} catch (IOException e) {
			e.printStackTrace();
			jsonResult.put("error", e.getMessage());
		}
		return jsonResult;
	}

	public JSONObject getThreshold() {
		_log.debug("Getting threshold values");
		JSONObject jsonResult = new JSONObject();
		Properties thresholdProps = new Properties();
		try {
			thresholdProps.load(getServletContext().getResourceAsStream(
					THRESHOLD_FILE));
			jsonResult.put(RequestConstants.SHARE_POOL_MIN,
					thresholdProps.get(RequestConstants.SHARE_POOL_MIN));
			jsonResult.put(RequestConstants.SHARE_POOL_MAX,
					thresholdProps.get(RequestConstants.SHARE_POOL_MAX));
			jsonResult.put(RequestConstants.BUFFERED_CACHE_MIN,
					thresholdProps.get(RequestConstants.BUFFERED_CACHE_MIN));
			jsonResult.put(RequestConstants.BUFFERED_CACHE_MAX,
					thresholdProps.get(RequestConstants.BUFFERED_CACHE_MAX));
			jsonResult.put(RequestConstants.REDO_LOG_BUFFER_MIN,
					thresholdProps.get(RequestConstants.REDO_LOG_BUFFER_MIN));
			jsonResult.put(RequestConstants.REDO_LOG_BUFFER_MAX,
					thresholdProps.get(RequestConstants.REDO_LOG_BUFFER_MAX));
			jsonResult.put(RequestConstants.MEMORY_SORTING_MIN,
					thresholdProps.get(RequestConstants.MEMORY_SORTING_MIN));
			jsonResult.put(RequestConstants.MEMORY_SORTING_MAX,
					thresholdProps.get(RequestConstants.MEMORY_SORTING_MAX));
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			jsonResult.put("error", e.getMessage());
		} catch (IOException e) {
			e.printStackTrace();
			jsonResult.put("error", e.getMessage());
		}
		return jsonResult;
	}

	private JSONObject getParameter() {
		_log.debug("Getting x, y, z values");
		JSONObject jsonResult = new JSONObject();
		if (DEBUG) {
			jsonResult.put("x", 60);
			jsonResult.put("y", 15);
			jsonResult.put("z", 1);
		} else {
			int x = SampleTimeConfiguration.getInstance().getX();
			int y = SampleTimeConfiguration.getInstance().getY();
			int z = SampleTimeConfiguration.getInstance().getZ();
			jsonResult.put("x", x);
			jsonResult.put("y", y);
			jsonResult.put("z", z);
		}
		return jsonResult;
	}

	private JSONObject setParameter(int x, int y, int z) {
		JSONObject jsonResult = new JSONObject();
		_log.debug("Setting x, y, z value to: " + x + ", " + y + ", " + z);
		try {
			dbDashboard.configureSampleTimes(x, y, z);
		} catch (Exception e) {
			e.printStackTrace();
			jsonResult.put("error", e.getMessage());
		}
		return jsonResult;
	}

	private JSONObject getPerformance(long startTime, long endTime,
			String level, String paramId) {
		_log.debug("Getting performance values:" + "\n\tStart time: "
				+ new Date(startTime) + "\n\tEnd time: " + new Date(endTime)
				+ "\n\tlevel: " + level + "\n\tParameter ID: " + paramId);
		Timestamp start = new Timestamp(startTime);
		Timestamp end = new Timestamp(endTime);
		JSONObject result = new JSONObject();
		if (level == null) {
			_log.debug("returning aggregate values");
			try {
				BigDecimal value;
				JSONArray valueList = new JSONArray();
				for (int i = 0; i < PARAM_IDS.length; i++) {
					_log.debug("Calling service getOverallPerformance with: "
							+ PARAM_IDS[i] + " " + start + " " + end);
					if (DEBUG) {
						value = new BigDecimal(random.nextInt(100));
					} else {
						value = dbDashboard.getOverallPerformance(PARAM_IDS[i],
								start, end);
					}
					valueList.put(value);
				}
				result.put("values", valueList);
			} catch (Exception e) {
				e.printStackTrace();
				result.put("error", e.getMessage());
			}
		} else {
			_log.debug("Get breakdown values");
			JSONArray arrayData = new JSONArray();
			try {
				JSONArray titleRow = new JSONArray();
				titleRow.put("Timestamp");
				String levelArg = ParameterConstant.BREAK_DOWN_LEVEL_ONE;
				switch (Integer.valueOf(level)) {
				case 0:
					levelArg = ParameterConstant.BREAK_DOWN_LEVEL_ONE;
					break;
				case 1:
					levelArg = ParameterConstant.BREAK_DOWN_LEVEL_TWO;
					break;
				case 2:
					levelArg = ParameterConstant.BREAK_DOWN_LEVEL_THREE;
					break;
				}
				if (paramId == null) {
					List<Map<Long, String>> details = new ArrayList<Map<Long, String>>();
					for (int i = 0; i < PARAM_IDS.length; i++) {

						if (DEBUG) {
							Map<Long, String> detail = new HashMap<Long, String>();
							titleRow.put(paramMap.get(PARAM_IDS[i]));
							for (int j = 0; j < 10; j++) {
								detail.put(new Long(j),
										String.valueOf(random.nextInt(100)));
							}
							details.add(detail);
						} else {
							Map<Long, String> temp = dbDashboard
									.getPerformanceBreakdown(PARAM_IDS[i],
											levelArg, start, end);
							if (temp != null && temp.size() != 0) {
								titleRow.put(paramMap.get(PARAM_IDS[i]));
								details.add(temp);
							}
						}
					}
					if (details.size() != 0) {
						arrayData.put(titleRow);
						SortedSet<Long> keys = new TreeSet<Long>(details.get(0)
								.keySet());
						for (Long key : keys) {
							JSONArray dataRow = new JSONArray();
							dataRow.put(df.format(new Date(key)));
							for (int j = 0; j < details.size(); j++) {
								String value = details.get(j).get(key);
								if (value != null) {
									dataRow.put(Double.valueOf(value));
								} else {
									dataRow.put(0);
								}
							}
							arrayData.put(dataRow);
						}
					} else {
						result.put("error", "no data to display.");
					}
					result.put("matrix", arrayData);
				} else {
					titleRow.put(paramMap.get(paramId));
					arrayData.put(titleRow);
					Map<Long, String> values = null;
					if (DEBUG) {
						values = new HashMap<Long, String>();
						for (int j = 0; j < 10; j++) {
							values.put(new Long(j),
									String.valueOf(random.nextInt(100)));
						}
					} else {
						values = dbDashboard.getPerformanceBreakdown(paramId,
								levelArg, start, end);
					}

					SortedSet<Long> keys = new TreeSet<Long>(values.keySet());
					for (Long key : keys) {
						JSONArray dataRow = new JSONArray();
						dataRow.put(df.format(new Date(key)));
						dataRow.put(Double.valueOf(values.get(key)));
						arrayData.put(dataRow);
					}
					result.put("matrix", arrayData);
				}
			} catch (Exception e) {
				e.printStackTrace();
				result.put("error", e.getMessage());
			}
		}
		return result;
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		String cmd = request.getParameter("cmd");
		JSONObject result = new JSONObject();
		if (cmd.equals(RequestConstants.SQL_DEBUG)) {
			result = getSQLResult(request
					.getParameter(RequestConstants.SQL_COMMAND));
		} else if (cmd.equals(RequestConstants.PARAM_SETTING)) {
			int x = Integer.valueOf(request
					.getParameter(RequestConstants.PARAM_X));
			int y = Integer.valueOf(request
					.getParameter(RequestConstants.PARAM_Y));
			int z = Integer.valueOf(request
					.getParameter(RequestConstants.PARAM_Z));
			result = setParameter(x, y, z);
		} else if (cmd.equals(RequestConstants.PARAM_GETTING)) {
			result = getParameter();
		} else if (cmd.equals(RequestConstants.THRESHOLD_SETTING)) {
			int sharePoolMin = Integer.valueOf(request
					.getParameter(RequestConstants.SHARE_POOL_MIN));
			int sharePoolMax = Integer.valueOf(request
					.getParameter(RequestConstants.SHARE_POOL_MAX));
			int buffedCacheMin = Integer.valueOf(request
					.getParameter(RequestConstants.BUFFERED_CACHE_MIN));
			int buffedCacheMax = Integer.valueOf(request
					.getParameter(RequestConstants.BUFFERED_CACHE_MAX));
			int redoLogMin = Integer.valueOf(request
					.getParameter(RequestConstants.REDO_LOG_BUFFER_MIN));
			int redoLogMax = Integer.valueOf(request
					.getParameter(RequestConstants.REDO_LOG_BUFFER_MAX));
			int memorySortMin = Integer.valueOf(request
					.getParameter(RequestConstants.MEMORY_SORTING_MIN));
			int memorySortMax = Integer.valueOf(request
					.getParameter(RequestConstants.MEMORY_SORTING_MAX));
			result = setThreshold(sharePoolMin, sharePoolMax, buffedCacheMin,
					buffedCacheMax, redoLogMin, redoLogMax, memorySortMin,
					memorySortMax);
		} else if (cmd.equals(RequestConstants.THRESHOLD_GETTING)) {
			result = getThreshold();
		} else if (cmd.equals(RequestConstants.PERFORMANCE)) {
			long startTime = Long.valueOf(request
					.getParameter(RequestConstants.START_TIME));
			long endTime = Long.valueOf(request
					.getParameter(RequestConstants.END_TIME));
			String level = request.getParameter(RequestConstants.LEVEL);
			String paramId = request.getParameter(RequestConstants.PARAM_ID);
			result = getPerformance(startTime, endTime, level, paramId);
		} else if (cmd.equals(RequestConstants.ADVICE)) {
			long timeStamp = Long.valueOf(request
					.getParameter(RequestConstants.TIME_STAMP));
			String paramId = request.getParameter(RequestConstants.PARAM_ID);
			int index = Integer.valueOf(request
					.getParameter(RequestConstants.INDEX));
			String value = request.getParameter(RequestConstants.VALUE);
			result = getAdvices(timeStamp, paramId);
			result.put(RequestConstants.INDEX, index);
			result.put(RequestConstants.VALUE, value);
		} else if (cmd.equals(RequestConstants.AWR_REPORT)) {
			long startTime = Long.valueOf(request
					.getParameter(RequestConstants.START_TIME));
			long endTime = Long.valueOf(request
					.getParameter(RequestConstants.END_TIME));
			result = getAwrReport(startTime, endTime);
		}
		response.setContentType("text/html");
		java.io.PrintWriter out = response.getWriter();
		out.print(result.toString());
		out.close();
	}

	private JSONObject getAwrReport(long startTime, long endTime) {
		_log.debug("Getting awr report from " + new Date(startTime) + " to "
				+ new Date(endTime));
		JSONObject result = new JSONObject();
		if (DEBUG) {
			if (random.nextBoolean()) {
				result.put("awr", new Date().toString());
			}
		} else {
			String message;
			try {
				message = dbDashboard.getAwrReport(new Timestamp(startTime),
						new Timestamp(endTime));
				result.put("awr", message.replaceAll("\n", "<br/>"));
			} catch (Exception e) {
				result.put("error", e.getMessage());
				e.printStackTrace();
			}
		}
		return result;
	}

	private JSONObject getAdvices(long timeStamp, String paramId) {
		_log.debug("Getting advice for parameter " + paramId + " on time "
				+ new Date(timeStamp));
		JSONObject result = new JSONObject();
		ArrayList<String> advices = new ArrayList<String>();
		if (DEBUG) {
			for (int i = 0; i < 10; i++) {
				advices.add("advices " + i + " for " + paramId + "\n");
			}
		} else {
			try {
				advices = dbDashboard.getAdvices(paramId, new Timestamp(
						timeStamp));
			} catch (Exception e) {
				e.printStackTrace();
				result.put("error", e.getMessage());
			}
		}
		StringBuffer sb = new StringBuffer();
		for (String advice: advices) {
			sb.append(advice.replaceAll("\n", "<br/>"));
		}
		try {
			result.put("advice", sb.toString());
		} catch (Exception e) {
			e.printStackTrace();
			result.put("error", e.getMessage());
		}
		return result;
	}
}
